var fs = require("fs");
var path = require("path");
const { exec } = require("../db/mysql");

const addExp = (expData = {}) => {
  const sql = "insert into exp set ?";

  return exec(sql, expData).then((insertData) => {
    if (insertData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const getExpList = () => {
  const sql = `select * from exp`;
  return exec(sql);
};
const delExp = (id) => {
  const sql = `delete from exp where exp_id=${id}`;
  const selSql = `select exp.exp_word,exp.exp_fj from exp where exp_id='${id}'`;
  const selExpSql = `select sel_exp.stu_word,sel_exp.stu_data from sel_exp where exp_id='${id}'`;
  return exec(selSql).then((rows) => {
    if (rows[0].exp_word || rows[0].exp_fj) {
      return exec(sql).then((delData) => {
        if (delData.affectedRows > 0) {
          fs.unlink(
            path.join(__dirname, `../public/word/${rows[0].exp_word}`),
            (err) => {
              if (err) {
                return false;
              }
            }
          );
          fs.unlink(
            path.join(__dirname, `../public/fj/${rows[0].exp_fj}`),
            (err) => {
              if (err) {
                return false;
              }
            }
          );
          return exec(selExpSql).then((rows) => {
            if (rows) {
              let len = rows.length;
              for (let i = 0; i < len; i++) {
                if (rows[i].stu_word) {
                  fs.unlink(
                    path.join(__dirname, `../public/word/${rows[i].stu_word}`),
                    (err) => {
                      if (err) {
                        return false;
                      }
                    }
                  );
                }
                if (rows[i].stu_data) {
                  fs.unlink(
                    path.join(__dirname, `../public/fj/${rows[i].stu_data}`),
                    (err) => {
                      if (err) {
                        return false;
                      }
                    }
                  );
                }
              }
              // exec(`delete from sel_exp where exp_id='${id}'`);
              return true;
            } else {
              return true;
            }
          });
        }
        return false;
      });
    }
  });
};
const updateExp = (id, expData = {}) => {
  let exp_name = expData.exp_name;
  let exp_grade = expData.exp_grade;
  let exp_class = expData.exp_class;
  let exp_msg = expData.exp_msg;
  let exp_time = expData.exp_time;
  let exp_word = expData.exp_word;
  let exp_fj = expData.exp_fj;

  const sql = `update exp set exp_name='${exp_name}',exp_grade='${exp_grade}' ,exp_class='${exp_class}',
  exp_msg='${exp_msg}',exp_time='${exp_time}',exp_word='${exp_word}',exp_fj='${exp_fj}' where exp_id=${id}`;
  return exec(sql).then((updateData) => {
    if (updateData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const selExp = (id) => {
  console.log(id);
  const sql = `select exp.*,teacher.tch_name from exp,teacher where exp_num='${id}' and 
  exp.tch_id=teacher.tch_id and exp.exp_code=1`;
  return exec(sql).then((rows) => {
    return rows[0];
  });
};
const selTchExping = (id) => {
  const sql = `select * from exp where tch_id='${id}' and exp_code=1`;
  return exec(sql);
};
const selTchExped = (id) => {
  const sql = `select * from exp where tch_id='${id}' and exp_code=0`;
  return exec(sql);
};
const selStuExping = (id) => {
  const sql = `SELECT exp.*,teacher.tch_name from exp,teacher,sel_exp WHERE exp.exp_id=sel_exp.exp_id and sel_exp.stu_id='${id}' and exp.tch_id = teacher.tch_id and exp.exp_code=1`;
  // const sql = `SELECT exp.*,teacher.tch_name from exp,teacher WHERE exp.exp_id in(SELECT sel_exp.exp_id from sel_exp WHERE stu_id='${id}' ) and exp.tch_id=teacher.tch_id and exp_code=1 GROUP BY exp_id `;
  return exec(sql);
};
const selStuExped = (id) => {
  const sql = `SELECT exp.*,sel_exp.* from exp,sel_exp WHERE sel_exp.stu_id='${id}' and exp.exp_code=0 and sel_exp.exp_id = exp.exp_id `;
  return exec(sql);
};
const selStuExpInfo = (expData = {}) => {
  let exp_id = expData.exp_id;
  let stu_id = expData.stu_id;
  const sql = `select * from sel_exp where exp_id=${exp_id} and stu_id='${stu_id}'`;
  return exec(sql);
};
const selExpByName = (expData = {}) => {
  let exp_name = expData.exp_name;
  let stu_id = expData.stu_id;
  const sql = `SELECT exp.*,sel_exp.* from exp,sel_exp WHERE sel_exp.stu_id='${stu_id}' 
  and exp.exp_code=0 and sel_exp.exp_id = exp.exp_id and exp.exp_name like '%${exp_name}%' `;
  return exec(sql);
};
const tSelExpByName = (expData = {}) => {
  let exp_name = expData.exp_name;
  let tch_id = expData.tch_id;
  const sql = `select * from exp where tch_id='${tch_id}' and exp_code=0 and exp_name like '%${exp_name}%'`;
  return exec(sql);
};

const getExpingStu = (id) => {
  const sql = `SELECT sel_exp.* ,student.stu_num,student.stu_name from sel_exp,student WHERE sel_exp.exp_id='${id}' and sel_exp.stu_id=student.stu_id`;
  return exec(sql);
};
const updateStuMark = (id, expData = {}) => {
  let exp_mark = expData.exp_mark;

  const sql = `update sel_exp set exp_mark='${exp_mark}' where sel_exp_id=${id}`;
  return exec(sql).then((updateData) => {
    if (updateData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const endExp = (id) => {
  const sql = `update exp set exp_code=0 where exp_id=${id}`;
  return exec(sql).then((updateData) => {
    if (updateData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const startExp = (id) => {
  const sql = `update exp set exp_code=1 where exp_id=${id}`;
  return exec(sql).then((updateData) => {
    if (updateData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const selExpAllMark = (id) => {
  const sql = `SELECT sel_exp.exp_mark from sel_exp where exp_id='${id}'`;
  return exec(sql);
};
const joinExp = (expData = {}) => {
  let exp_id = expData.exp_id;
  let stu_id = expData.stu_id;
  let exp_mark = expData.exp_mark;
  const sql = `insert into sel_exp (exp_id,stu_id,exp_mark) 
    values('${exp_id}','${stu_id}','${exp_mark}');`;
  return exec(sql, expData).then((insertData) => {
    if (insertData.affectedRows > 0) {
      return true;
    }
    return false;
  });
};
const isJoin = (expData = {}) => {
  let exp_num = expData.exp_num;
  // let exp_id = 1;
  let stu_id = expData.stu_id;

  const sql = `select * from sel_exp where stu_id='${stu_id}' and 
  exp_id=(select exp.exp_id from exp where exp_num='${exp_num}')`;
  return exec(sql).then((rows) => {
    return rows[0] || {};
  });
};
const uploadWord = (expData = {}) => {
  let sel_exp_id = expData.sel_exp_id;
  let stu_word = expData.stu_word;
  const Selsql = `select sel_exp.stu_word from sel_exp where sel_exp_id=${sel_exp_id}`;
  const sql = `update sel_exp set stu_word='${stu_word}' where sel_exp_id=${sel_exp_id}`;

  return exec(Selsql).then((rows) => {
    if (!rows[0].stu_word) {
    } else {
      fs.unlink(
        path.join(__dirname, `../public/word/${rows[0].stu_word}`),
        (err) => {
          if (err) {
            return false;
          }
        }
      );
    }
    return exec(sql).then((delData) => {
      if (delData.affectedRows > 0) {
        return true;
      }
      return false;
    });
  });
};
const uploadFj = (expData = {}) => {
  let sel_exp_id = expData.sel_exp_id;
  let stu_data = expData.stu_data;
  console.log(expData);
  const Selsql = `select sel_exp.stu_data from sel_exp where sel_exp_id=${sel_exp_id}`;
  const sql = `update sel_exp set stu_data='${stu_data}' where sel_exp_id=${sel_exp_id}`;

  return exec(Selsql).then((rows) => {
    if (!rows[0].stu_data) {
    } else {
      fs.unlink(
        path.join(__dirname, `../public/fj/${rows[0].stu_data}`),
        (err) => {
          if (err) {
            return false;
          }
        }
      );
    }
    return exec(sql).then((delData) => {
      if (delData.affectedRows > 0) {
        return true;
      }
      return false;
    });
  });
};
module.exports = {
  addExp,
  getExpList,
  delExp,
  updateExp,
  selExp,
  selTchExping,
  selTchExped,
  selStuExping,
  selStuExped,
  selStuExpInfo,
  selExpByName,
  tSelExpByName,
  getExpingStu,
  updateStuMark,
  endExp,
  startExp,
  selExpAllMark,
  joinExp,
  isJoin,
  uploadWord,
  uploadFj,
};
